package com.project.service.dao.impl;

import com.project.service.bean.LinkBean;
import com.project.service.dao.ILinkDao;

import java.sql.*;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;

/**
 * 通迅录持久接口实现类
 */
public class LinkDaoImpl implements ILinkDao {
    @Override
    public void add(LinkBean linkObj) {
        //连接对象
        Connection con = null;
        //SQL语句执行对象
        PreparedStatement ps = null;
        try {
            //加载驱动
            Class.forName("com.mysql.jdbc.Driver");
            //建立连接  localhost表示连接数据库所在服务器的IP地址  6789为连接端口
            //db表示连接mysql 数据库的库名 characterEncoding=utf-8 设置编码集
            //root表示登陆mysql的用户名   lovo表示登陆mysql的密码
            con = DriverManager.getConnection(
                    "jdbc:mysql://localhost:6789/db?characterEncoding=utf-8",
                        "root","lovo");

            //执行SQL语句
            ps = con.prepareStatement("insert into t_link(l_name,l_birthday,l_sex,l_phone)" +
                    " values(?,?,?,?)");
            //填充占位符
            ps.setObject(1,linkObj.getName());
            ps.setObject(2,linkObj.getBirthday());
            ps.setObject(3,linkObj.getSex());
            ps.setObject(4,linkObj.getPhone());

            //更新数据库，将数据真正写入数据库
            ps.executeUpdate();

        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            try {
                //关闭连接
                ps.close();
                con.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    @Override
    public void del(int id) {
        Connection con = null;
        PreparedStatement ps = null;

        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection(
                    "jdbc:mysql://localhost:6789/db?characterEncoding=utf-8",
                    "root","lovo");

            ps  = con.prepareStatement("delete from t_link where pk_linkId=?");
            ps.setObject(1,id);

            ps.executeUpdate();

        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            try {
                ps.close();
                con.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

    }

    @Override
    public void update(int id, String phone) {
        Connection con = null;
        PreparedStatement ps = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost:6789/db?characterEncoding=utf-8","root","lovo");

            ps = con.prepareStatement("update t_link set l_phone=? where pk_linkId=?");
            ps.setObject(1,phone);
            ps.setObject(2,id);

            ps.executeUpdate();

        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            try {
                ps.close();
                con.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    @Override
    public List<LinkBean> findAll() {
        List<LinkBean> list = new ArrayList<>();
        Connection con = null;
        PreparedStatement ps = null;
        //结果集
        ResultSet rs = null;

        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection(
                    "jdbc:mysql://localhost:6789/db?characterEncoding=utf-8",
                    "root","lovo");

            ps = con.prepareStatement("select * from t_link");
            //将查询结果，存放至结果集对象中
            rs = ps.executeQuery();

            //将结果集指针指向下一条记录，如果返回值为false，表示没有下一条记录，遍历结束
            while(rs.next()){
                LinkBean linkObj = new LinkBean();
                linkObj.setId(rs.getInt("pk_linkId"));
                linkObj.setName(rs.getString("l_name"));
                linkObj.setBirthday(LocalDate.parse(rs.getString("l_birthday")));
                linkObj.setSex(rs.getString("l_sex"));
                linkObj.setPhone(rs.getString("l_phone"));

                list.add(linkObj);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            try {
                rs.close();
                ps.close();
                con.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }


        return list;
    }

    @Override
    public LinkBean findById(int id) {
        LinkBean linkObj = null;
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost:6789/db?characterEncoding=utf-8","root","lovo");

            ps = con.prepareStatement("select * from t_link where pk_linkId=?");
            ps.setObject(1,id);
            rs = ps.executeQuery();

//            if(rs.next()) {
                linkObj = new LinkBean();
                linkObj.setId(rs.getInt("pk_linkId"));
                linkObj.setName(rs.getString("l_name"));
                linkObj.setBirthday(LocalDate.parse(rs.getString("l_birthday")));
                linkObj.setSex(rs.getString("l_sex"));
                linkObj.setPhone(rs.getString("l_phone"));
//            }

        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            try {
                rs.close();
                ps.close();
                con.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        return linkObj;
    }

    @Override
    public List<LinkBean> findByName(String name) {
        List<LinkBean> list = new ArrayList<>();
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost:6789/db?characterEncoding=utf-8","root","lovo");

//            String sql = "select * from t_link where l_name like '%"+name+"%'";
//            System.out.println(sql);
            ps = con.prepareStatement( "select * from t_link where l_name like ?");
            ps.setObject(1,name);

            rs = ps.executeQuery();
            while(rs.next()){
                LinkBean linkObj = new LinkBean();
                linkObj.setId(rs.getInt("pk_linkId"));
                linkObj.setName(rs.getString("l_name"));
                linkObj.setBirthday(LocalDate.parse(rs.getString("l_birthday")));
                linkObj.setSex(rs.getString("l_sex"));
                linkObj.setPhone(rs.getString("l_phone"));

                list.add(linkObj);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            try {
                rs.close();
                ps.close();
                con.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        return list;
    }

    public static void main(String[] args) {
        ILinkDao dao = new LinkDaoImpl();
//        dao.add(new LinkBean("郑寿", "男",LocalDate.parse("1995-09-25"),"18039483742"));
//        dao.del(5);
//        dao.update(1,"18920392884");
//        System.out.println(dao.findAll());
//        System.out.println(dao.findById(1));
        System.out.println(dao.findByName("赵' "));
    }
}
